﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;

namespace Common
{
    /// <summary>
    /// 
    /// </summary>
    public class DbTypeConverter
    {
        private static Dictionary<SqlDbType,Type> _SqlType_Type_Mappings;
        /// <summary></summary>
        public static Dictionary<SqlDbType, Type> SqlType_Type_Mappings
        {
            get
            {
                return _SqlType_Type_Mappings;
            }
            set
            {
                _SqlType_Type_Mappings = value;
            }
        }

        private static Dictionary<OracleType, SqlDbType> _OraType_SqlType_Mappings;
        /// <summary></summary>
        public static Dictionary<OracleType, SqlDbType> OraType_SqlType_Mappings
        {
            get
            {
                return _OraType_SqlType_Mappings;
            }
            set
            {
                _OraType_SqlType_Mappings = value;
            }
        }

        private static Dictionary<SqlDbType, OracleType> _SqlType_OraType_Mappings;
        /// <summary></summary>
        public static Dictionary<SqlDbType, OracleType> SqlType_OraType_Mappings
        {
            get
            {
                return _SqlType_OraType_Mappings;
            }
            set
            {
                _SqlType_OraType_Mappings = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        static DbTypeConverter()
        {
            _SqlType_Type_Mappings=new Dictionary<SqlDbType, Type>();
            _SqlType_OraType_Mappings=new Dictionary<SqlDbType, OracleType>();
            _OraType_SqlType_Mappings=new Dictionary<OracleType, SqlDbType>();

            SqlDbType[] sqlTypes =
                new SqlDbType[]
                    {
                        SqlDbType.BigInt,
                        SqlDbType.Binary,
                        SqlDbType.Bit,
                        SqlDbType.Char,
                        SqlDbType.Date,
                        SqlDbType.DateTime,
                        SqlDbType.DateTime2,
                        SqlDbType.DateTimeOffset,
                        SqlDbType.Decimal,
                        SqlDbType.Float,
                        SqlDbType.Image,
                        SqlDbType.Int,
                        SqlDbType.Money,
                        SqlDbType.NChar,
                        SqlDbType.NText,
                        SqlDbType.NVarChar,
                        SqlDbType.Real,
                        SqlDbType.SmallDateTime,
                        SqlDbType.SmallInt,
                        SqlDbType.SmallMoney,
                        SqlDbType.Structured,
                        SqlDbType.Text,
                        SqlDbType.Time,
                        SqlDbType.Timestamp,
                        SqlDbType.TinyInt,
                        SqlDbType.Udt,
                        SqlDbType.UniqueIdentifier,
                        SqlDbType.VarBinary,
                        SqlDbType.VarChar,
                        SqlDbType.Variant,
                        SqlDbType.Xml
                    };

            OracleType[] oraTypes =
                new OracleType[]
                    {
                        OracleType.BFile,
                        OracleType.Blob,
                        OracleType.Int16,
                        OracleType.Char,
                        OracleType.Clob,
                        OracleType.Cursor,
                        OracleType.DateTime,
                        OracleType.Double,
                        OracleType.Float,
                        OracleType.Int32,
                        OracleType.LongRaw,
                        OracleType.NChar,
                        OracleType.NClob,
                        OracleType.Number,
                        OracleType.NVarChar,
                        OracleType.Raw,
                        OracleType.RowId,
                        OracleType.Timestamp,
                        OracleType.TimestampWithTZ,
                        OracleType.TimestampLocal,
                        OracleType.VarChar,
                    };

            for(int i=0;i<sqlTypes.Length;i++)
            {
                SqlDbType sType = sqlTypes[i];
                switch (sType)
                {
                    case SqlDbType.BigInt:
                        _SqlType_Type_Mappings.Add(sType, typeof (Int64));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Number);
                        break;
                    case SqlDbType.Binary:
                        _SqlType_Type_Mappings.Add(sType, typeof (byte[]));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Blob);
                        break;
                    case SqlDbType.Bit:
                        _SqlType_Type_Mappings.Add(sType, typeof (bool));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Int16);
                        break;
                    case SqlDbType.Char:
                        _SqlType_Type_Mappings.Add(sType, typeof (char));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Char);
                        break;
                    case SqlDbType.Date:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.DateTime);
                        break;
                    case SqlDbType.DateTime:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.DateTime);
                        break;
                    case SqlDbType.DateTime2:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.DateTime);
                        break;
                    case SqlDbType.Decimal:
                        _SqlType_Type_Mappings.Add(sType, typeof (decimal));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Double);
                        break;
                    case SqlDbType.Float:
                        _SqlType_Type_Mappings.Add(sType, typeof (float));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Float);
                        break;
                    case SqlDbType.Image:
                        _SqlType_Type_Mappings.Add(sType, typeof (byte[]));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Blob);
                        break;
                    case SqlDbType.Int:
                        _SqlType_Type_Mappings.Add(sType, typeof (int));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Int32);
                        break;
                    case SqlDbType.Money:
                        _SqlType_Type_Mappings.Add(sType, typeof (double));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Number);
                        break;
                    case SqlDbType.NChar:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.NChar);
                        break;
                    case SqlDbType.NText:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Clob);
                        break;
                    case SqlDbType.NVarChar:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.NVarChar);
                        break;
                    case SqlDbType.Real:
                        _SqlType_Type_Mappings.Add(sType, typeof (double));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Double);
                        break;
                    case SqlDbType.SmallDateTime:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.DateTime);
                        break;
                    case SqlDbType.SmallInt:
                        _SqlType_Type_Mappings.Add(sType, typeof (Int16));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Int16);
                        break;
                    case SqlDbType.SmallMoney:
                        _SqlType_Type_Mappings.Add(sType, typeof (double));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Number);
                        break;
                    case SqlDbType.Structured:
                        _SqlType_Type_Mappings.Add(sType, typeof (object));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Raw);
                        break;
                    case SqlDbType.Text:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Clob);
                        break;
                    case SqlDbType.Time:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.DateTime);
                        break;
                    case SqlDbType.Timestamp:
                        _SqlType_Type_Mappings.Add(sType, typeof (DateTime));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Timestamp);
                        break;
                    case SqlDbType.TinyInt:
                        _SqlType_Type_Mappings.Add(sType, typeof (Int16));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Int16);
                        break;
                    case SqlDbType.UniqueIdentifier:
                        _SqlType_Type_Mappings.Add(sType, typeof (Guid));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.VarChar);
                        break;
                    case SqlDbType.VarBinary:
                        _SqlType_Type_Mappings.Add(sType, typeof (byte[]));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Blob);
                        break;
                    case SqlDbType.VarChar:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.VarChar);
                        break;
                    case SqlDbType.Variant:
                        _SqlType_Type_Mappings.Add(sType, typeof (byte[]));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Blob);
                        break;
                    case SqlDbType.Xml:
                        _SqlType_Type_Mappings.Add(sType, typeof (string));
                        _SqlType_OraType_Mappings.Add(sType, OracleType.Clob);
                        break;
                    default:
                        break;
                }
            }

            for(int i=0;i<oraTypes.Length;i++)
            {
                OracleType oType = oraTypes[i];
                switch (oType)
                {
                    case OracleType.BFile:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Image);
                        break;
                    case OracleType.Blob:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Image);
                        break;
                    case OracleType.Char:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Char);
                        break;
                    case OracleType.Clob:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.VarChar);
                        break;
                    case OracleType.Cursor:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Variant);
                        break;
                    case OracleType.DateTime:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.DateTime);
                        break;
                    case OracleType.Double:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Real);
                        break;
                    case OracleType.Float:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Float);
                        break;
                    case OracleType.Int32:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Int);
                        break;
                    case OracleType.LongRaw:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Variant);
                        break;
                    case OracleType.NChar:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.NChar);
                        break;
                    case OracleType.NClob:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.NVarChar);
                        break;
                    case OracleType.Number:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Decimal);
                        break;
                    case OracleType.NVarChar:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.NVarChar);
                        break;
                    case OracleType.Raw:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Variant);
                        break;
                    case OracleType.RowId:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.Variant);
                        break;
                    case OracleType.VarChar:
                        _OraType_SqlType_Mappings.Add(oType, SqlDbType.VarChar);
                        break;
                    default:
                        break;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static SqlDbType GetSqlDbTypeFromType(Type type)
        {
            foreach(SqlDbType sType in _SqlType_Type_Mappings.Keys)
            {
                if(_SqlType_Type_Mappings[sType]==type)
                    return sType;
            }

            return SqlDbType.VarChar;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oType"></param>
        /// <returns></returns>
        public static SqlDbType GetSqlDbTypeFromOracleType(OracleType oType)
        {
            if (_OraType_SqlType_Mappings.ContainsKey(oType))
                return _OraType_SqlType_Mappings[oType];
            else
            {
                return SqlDbType.NVarChar;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sType"></param>
        /// <returns></returns>
        public static Type FromSqlType(SqlDbType sType)
        {
            if(_SqlType_Type_Mappings.ContainsKey(sType))
            {
                return _SqlType_Type_Mappings[sType];
            }
            else
            {
                return typeof (string);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oType"></param>
        /// <returns></returns>
        public static Type FromOracleType(OracleType oType)
        {
            SqlDbType sType = GetSqlDbTypeFromOracleType(oType);
            return FromSqlType(sType);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static OracleType GetOracleTypeFromType(Type type)
        {
            SqlDbType sType = GetSqlDbTypeFromType(type);
            return GetOracleTypeFromSqlDbType(sType);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sType"></param>
        /// <returns></returns>
        public static OracleType GetOracleTypeFromSqlDbType(SqlDbType sType)
        {
            if (_SqlType_OraType_Mappings.ContainsKey(sType))
                return _SqlType_OraType_Mappings[sType];
            else
                return OracleType.NVarChar;
        }
    }
}
